package com.innovation.util.config;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExportSchema {

	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws SQLException {
		List<String> tables = new ArrayList<String>();
		tables.add("config_talent_type");
		tables.add("config_talent");
		tables.add("config_item");
		save2Excel(null);
	}
	
	/**
	 * 根据指定的表名到处表结构和表数据
	 * @param tableNames
	 * @throws SQLException
	 */
	public static void save2Excel(List<String> tableNames) throws SQLException{
		long startTime = System.currentTimeMillis();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		// 创建Excel的工作书册 Workbook,对应到一个excel文档
	    HSSFWorkbook wb = new HSSFWorkbook();
	    
		String userName = "ppseaer";
		String password = "ppseaer@ppsea.com";
		String dbUrl1 = "jdbc:mysql://127.0.0.1:3306/snake_res";
		Connection conn = DriverManager.getConnection(dbUrl1,userName, password);
		
		DatabaseMetaData databaseMetaData = conn.getMetaData();
		ResultSet tableSet = databaseMetaData.getTables(null, "%","%",new String[]{"TABLE"});
		int tableCount = 0;
		while (tableSet.next()) {
			String tableName = tableSet.getString("TABLE_NAME");
			if(!tableName.startsWith("config_")){
				continue;
			}
			
			if(tableNames != null && !tableNames.contains(tableName)){
				continue;
			}
			
			tableCount++;//表个数标记
			System.out.println(tableCount+".exporting table:"+tableName+"......");
			
			//创建一个worksheet
			HSSFSheet sheet = wb.createSheet(tableName);
			sheet.setAutobreaks(true);
			
			//创建两行(1列名 2列注释)
			HSSFRow rowColumnName = sheet.createRow(0);
			HSSFRow rowColumnRemarks = sheet.createRow(1);
			ResultSet columnSet = databaseMetaData.getColumns(null, "%", tableName, "%");
			int colCount = 0;
			HSSFCell cell = null;
			
			//写入列以及列注释
			List<String> columns = new ArrayList<String>();
			while(columnSet.next()){
				String columnName = columnSet.getString("COLUMN_NAME");//列名
				String columnComment = columnSet.getString("REMARKS");//列注释
				
				//列名
				cell = rowColumnName.createCell(colCount);
				cell.setCellValue(columnName);
				
				//列注释
				cell = rowColumnRemarks.createCell(colCount);
				cell.setCellValue(columnComment);
				
				columns.add(columnName);
				colCount++;
			}
			
			//取出表数据并写入到excel中
			int startRowNo = 2; //起始行标示 
			int rowCount = 0;//记录行数
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery("select * from "+tableName);
			while(rs.next()){
				rowCount++;
				HSSFRow row = sheet.createRow(startRowNo);
				for(int i = 0; i < columns.size(); i++){
					cell = row.createCell(i);
					cell.setCellValue(rs.getString(columns.get(i)));
				}
				startRowNo++;
			}
			rs.close();
			columnSet.close();
			
			//导完1张表
			System.out.println("  export finished table:"+tableName+" totalRow:"+rowCount);
			System.out.println();
		}
		tableSet.close();
		conn.close();
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
		Calendar cal = Calendar.getInstance();
		String time = sdf.format(cal.getTime());
		String filePath = System.getProperty("user.dir") + File.separator +"db" + File.separator+"excel"+File.separator+"snake_res_"+time+".xls";
		File file = new File(filePath);
	    try {
		    if(file.exists() && !file.isDirectory()){
				file.delete();
		    }
		    file.createNewFile();
		    FileOutputStream os = new FileOutputStream(filePath);
		    wb.write(os);
		    os.close();
	    } catch (IOException e) {
			e.printStackTrace();
		}
	    System.out.println("export finished, useTime="+(System.currentTimeMillis() - startTime));
	}

}
